Tableau Prep File Name: Superstore.tflx
Background: 

                           		
                        

                     
                  

                  

                     
                        

                           Requested by:
                           
                              

                           		
                           Requested date:
                           
                              

                           		
                        

                     
                  

                  

                     
                        

                           Developed by:
                           
                              

                           		
                           Completed date:
                           
                              

                           		
                        

                     
                  

                  

                     
                        

                           Audience:
                           
                              

                           		
                        

                     
                  

                  

                     

                        
                     

                  

               		
            

         

      

      

         Overall
               Summary
               Stats
         
         
            

               
                  

               		
               
                  
Counts

               		
            

         
Connections #6
Input Steps #6
Output Steps #2
Input Fields #122
Union Steps #1
Join Steps #2
Pivot Steps #1
Aggregate Steps #1
Clean Steps #6
Actions #38

         
         
      

      

         
            Connections
         
         
            

               
                  
Name

               		
               
                  
Connection Type

               		
               
                  
Is Packaged?

               		
               
                  
File Name

               		
            

         
         
         
                                            Orders_Central.csv                                           .v1.SqlConnection                                           true                                                                                   
                                            orders_south_2015.csv                                           .v1.SqlConnection                                           true                                                                                   
                                            return reasons_new.xlsx                                           .v1.SqlConnection                                           true                                                                                   
                                            Orders_West.csv                                           .v1.SqlConnection                                           true                                                                                   
                                            Orders_East.xlsx                                           .v1.SqlConnection                                           true                                                                                   
                                            Quota.xlsx                                           .v1.SqlConnection                                           true                                                                                   

      

      
      
Flow to Create 'Annual Regional Performance.hyper' Output
Flow Diagram
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Section  1 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Quota
 
Pivot Quotas
 
1.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          5
        

        

          Pivot Steps #
          1
        

        

          Actions #
          1
        
1.2. Quota - Input Step
Step Type
Connection Name

          

            LoadExcel
            Quota.xlsx
            
            
            
          

        
1.2.1 Input Fields
Field Name
Field Type
Collation
                    Region                   string                   LEN_RIN_S2                
                    2015                   integer                                   
                    2016                   integer                                   
                    2017                   integer                                   
                    2018                   integer                                   
1.3. Pivot Quotas - Pivot Step
Step Name
Actions - After Pivot
Groups
Use Smart Defaults
                         Pivot QuotasChange Year to Number (whole) 1 (ChangeColumnType) - Default[{"expressions":[{"bindings":[{"bindingType":"literal","newColumnName":"Year","groupName":"2015"},{"bindingType":"column","newColumnName":"Quota","columnName":"2015"}]},{"bindings":[{"bindingType":"literal","newColumnName":"Year","groupName":"2016"},{"bindingType":"column","newColumnName":"Quota","columnName":"2016"}]},{"bindings":[{"bindingType":"literal","newColumnName":"Year","groupName":"2018"},{"bindingType":"column","newColumnName":"Quota","columnName":"2018"}]},{"bindings":[{"bindingType":"literal","newColumnName":"Year","groupName":"2017"},{"bindingType":"column","newColumnName":"Quota","columnName":"2017"}]}]}]                        false
1.3.1 Change Column Type Action
Action Name
Column Name
Type
Calculation
Change Year to Number (whole) 1Year                           integer                           
Section  2 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Orders (Central)
 
Fix Dates
 
2.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          24
        

        

          Clean Steps #
          1
        

        

          Actions #
          13
        
2.2. Orders (Central) - Input Step
Step Type
Connection Name
Contains Header

          

            LoadCsv
            Orders_Central.csv
            
            true
            
          

        
2.2.1 Input Fields
Field Name
Field Type
Collation
                    Row ID                   integer                                   
                    Order ID                   string                   LEN_RUS                
                    Ship Mode                   string                   LEN_RUS                
                    Customer ID                   string                   LEN_RUS                
                    Customer Name                   string                   LEN_RUS                
                    Segment                   string                   LEN_RUS                
                    Country                   string                   LEN_RUS                
                    City                   string                   LEN_RUS                
                    State                   string                   LEN_RUS                
                    Postal Code                   integer                                   
                    Product ID                   string                   LEN_RUS                
                    Category                   string                   LEN_RUS                
                    Sub-Category                   string                   LEN_RUS                
                    Product                   string                   LEN_RUS                
                    Sales                   real                                   
                    Quantity                   integer                                   
                    Profit                   real                                   
                    Order Year                   integer                                   
                    Order Month                   integer                                   
                    Order Day                   integer                                   
                    Ship Year                   integer                                   
                    Ship Month                   integer                                   
                    Ship Day                   integer                                   
                    Discounts                   string                   LEN_RUS                

       

           
               2.3. Fix Dates - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         Remove Order Day                        RemoveColumn                    
                         renamed Discounts to Discount                        RenameColumn                    
                         Remove Ship Month                        RemoveColumn                    
                         Remove Ship Day                        RemoveColumn                    
                         Add Order Date                        AddColumn                    
                         change Order Date to date                        ChangeColumnType                    
                         Remove Order Month                        RemoveColumn                    
                         change Ship Date to date                        ChangeColumnType                    
                         Add Region                        AddColumn                    
                         Remove Order Year                        RemoveColumn                    
                         Add Ship Date                        AddColumn                    
                         renamed Product to Product Name                        RenameColumn                    
                         Remove Ship Year                        RemoveColumn                    

       

   
2.3.1 Remove Column Action
Action Name
Column Name
Remove Order DayOrder Day
Remove Ship MonthShip Month
Remove Ship DayShip Day
Remove Order MonthOrder Month
Remove Order YearOrder Year
Remove Ship YearShip Year
2.3.2 Rename Column Action
Action Name
Column Name
Rename
renamed Discounts to DiscountDiscounts                           Discount
renamed Product to Product NameProduct                           Product Name
2.3.3 Add Column Action
Action Name
Column Name
Expression
Add Order DateOrder Date                           (STR([Order Day])+"/"+STR([Order Month])+"/"+STR([Order Year]))
Add RegionRegion                           "Central"
Add Ship DateShip Date                           STR([Ship Day])+"/"+STR([Ship Month])+"/"+STR([Ship Year])
2.3.4 Change Column Type Action
Action Name
Column Name
Type
Calculation
change Order Date to dateOrder Date                           date                           IF NOT ISNULL(DATE(DATEPARSE("dd/MM/yyyy",[Order Date]))) THEN DATE(DATEPARSE("dd/MM/yyyy",[Order Date])) ELSEIF NOT ISNULL(DATE([Order Date])) THEN DATE([Order Date]) ELSE NULL END
change Ship Date to dateShip Date                           date                           IF NOT ISNULL(DATE(DATEPARSE("dd/MM/yyyy",[Ship Date]))) THEN DATE(DATEPARSE("dd/MM/yyyy",[Ship Date])) ELSEIF NOT ISNULL(DATE([Ship Date])) THEN DATE([Ship Date]) ELSE NULL END
Section  3 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
orders_south_2015
 
Remove Nulls
 
3.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          22
        

        

          Clean Steps #
          1
        

        

          Actions #
          1
        
3.2. orders_south_2015 - Input Step
Step Type
Connection Name
Contains Header

          

            LoadCsvInputUnion
            orders_south_2015.csv
            
            true
            
          

        
3.2.1 Input Fields
Field Name
Field Type
Collation
                    Sales                   real                                   
                    Quantity                   integer                                   
                    Profit                   real                                   
                    Discount                   real                                   
                    Region                   string                   LEN_RUS                
                    State                   string                   LEN_RUS                
                    Row ID                   integer                                   
                    Order ID                   string                   LEN_RUS                
                    Order Date                   datetime                                   
                    Ship Date                   datetime                                   
                    Ship Mode                   string                   LEN_RUS                
                    Customer ID                   string                   LEN_RUS                
                    Customer Name                   string                   LEN_RUS                
                    Segment                   string                   LEN_RUS                
                    Country                   string                   LEN_RUS                
                    City                   string                   LEN_RUS                
                    Postal Code                   integer                                   
                    Product ID                   string                   LEN_RUS                
                    Category                   string                   LEN_RUS                
                    Sub-Category                   string                   LEN_RUS                
                    Product Name                   string                   LEN_RUS                
                    File Paths                   string                   LEN_RUS                

       

           
               3.3. Remove Nulls - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         Exclude Order ID:null                        RangeFilter                    

       

   
3.3.1 Range Filter Action
Action Name
Ranges
Exclude Order ID:nullOrder ID-->[{"startValue":null,"includeStart":true,"endValue":null,"includeEnd":true}]
Section  4 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Orders_East
 
Fix Data Type
 
4.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          21
        

        

          Clean Steps #
          1
        

        

          Actions #
          3
        
4.2. Orders_East - Input Step
Step Type
Connection Name

          

            LoadExcel
            Orders_East.xlsx
            
            
            
          

        
4.2.1 Input Fields
Field Name
Field Type
Collation
                    Category                   string                   LEN_RIN_S2                
                    City                   string                   LEN_RIN_S2                
                    Country                   string                   LEN_RIN_S2                
                    Customer ID                   string                   LEN_RIN_S2                
                    Customer Name                   string                   LEN_RIN_S2                
                    Discount                   real                                   
                    Order Date                   date                                   
                    Order ID                   string                   LEN_RIN_S2                
                    Postal Code                   integer                                   
                    Product ID                   string                   LEN_RIN_S2                
                    Product Name                   string                   LEN_RIN_S2                
                    Profit                   real                                   
                    Quantity                   integer                                   
                    Region                   string                   LEN_RIN_S2                
                    Row ID                   integer                                   
                    Segment                   string                   LEN_RIN_S2                
                    Ship Date                   date                                   
                    Ship Mode                   string                   LEN_RIN_S2                
                    State                   string                   LEN_RIN_S2                
                    Sub-Category                   string                   LEN_RIN_S2                
                    Sales                   string                   LEN_RIN_S2                

       

           
               4.3. Fix Data Type - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         change Discount to string 1                        ChangeColumnType                    
                         Quick Calc 1                        QuickCalcColumn                    
                         change Sales to real 1                        ChangeColumnType                    

       

   
4.3.1 Change Column Type Action
Action Name
Column Name
Type
Calculation
change Discount to string 1Discount                           string                           
change Sales to real 1Sales                           real                           
4.3.2 Calculated Field Action
Action Name
Column Name
Expression
Calc. Expression Type
Quick Calc 1Sales                           REGEXP_REPLACE([Sales], '[:Letter:]', '')                           RemoveLetters
Section  5 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Orders (West)
 
Rename States
 
5.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          41
        

        

          Clean Steps #
          1
        

        

          Actions #
          2
        
5.2. Orders (West) - Input Step
Step Type
Connection Name
Actions
Contains Header

          

            LoadCsv
            Orders_West.csv
            RemoveColumns
            true
            
          

        
5.2.1 Input Fields
Field Name
Field Type
Collation
                    Row ID                   integer                                   
                    Order ID                   string                   LEN_RUS                
                    Order Date                   datetime                                   
                    Ship Date                   datetime                                   
                    Ship Mode                   string                   LEN_RUS                
                    Customer ID                   string                   LEN_RUS                
                    Customer Name                   string                   LEN_RUS                
                    Segment                   string                   LEN_RUS                
                    Country                   string                   LEN_RUS                
                    City                   string                   LEN_RUS                
                    Postal Code                   integer                                   
                    Region                   string                   LEN_RUS                
                    Product ID                   string                   LEN_RUS                
                    Category                   string                   LEN_RUS                
                    Sub-Category                   string                   LEN_RUS                
                    Product Name                   string                   LEN_RUS                
                    Sales                   real                                   
                    Quantity                   integer                                   
                    Discount                   real                                   
                    Profit                   real                                   
                    Right_Row ID                   integer                                   
                    Right_Order Date                   datetime                                   
                    Right_Ship Date                   datetime                                   
                    Right_Ship Mode                   string                   LEN_RUS                
                    Right_Customer ID                   string                   LEN_RUS                
                    Right_Customer Name                   string                   LEN_RUS                
                    Right_Segment                   string                   LEN_RUS                
                    Right_Country                   string                   LEN_RUS                
                    Right_City                   string                   LEN_RUS                
                    Right_State2                   string                   LEN_RUS                
                    Right_Postal Code                   integer                                   
                    Right_Region                   string                   LEN_RUS                
                    Right_Product ID                   string                   LEN_RUS                
                    Right_Category                   string                   LEN_RUS                
                    Right_Sub-Category                   string                   LEN_RUS                
                    Right_Product Name                   string                   LEN_RUS                
                    Right_Sales                   real                                   
                    Right_Quantity                   integer                                   
                    Right_Discount                   real                                   
                    Right_Profit                   real                                   
                    State                   string                   LEN_RUS                
5.2.1 Remove Columns Action
Column Names
Right_Row ID
Right_Order Date
Right_Ship Date
Right_Ship Mode
Right_Customer ID
Right_Customer Name
Right_Segment
Right_Country
Right_City
Right_State2
Right_Postal Code
Right_Region
Right_Product ID
Right_Category
Right_Sub-Category
Right_Product Name
Right_Sales
Right_Quantity
Right_Discount
Right_Profit

       

           
               5.3. Rename States - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         18 values replaced with 9 values.                        Remap                    

       

   
5.3.1 Remap Action
Action Name
Column Name
Values
18 values replaced with 9 values.State                           {"\"Arizona\"":["\"AZ\"","\"Arizona\""],"\"California\"":["\"CA\"","\"California\""],"\"Colorado\"":["\"CO\"","\"Colorado\""],"\"Idaho\"":["\"ID\"","\"Idaho\""],"\"Montana\"":["\"MT\"","\"Montana\""],"\"New Mexico\"":["\"NM\"","\"New Mexico\""],"\"Oregon\"":["\"OR\"","\"Oregon\""],"\"Washington\"":["\"WA\"","\"Washington\""],"\"Utah\"":["\"UT\"","\"Utah\""]}                        
Section  6 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
All Orders
 
6.1 Summary Stats
Counts

        

          Union Steps #
          1
        
6.2. All Orders - Union Step
Step Name
Sources
                       All OrdersFix Dates
Remove Nulls
Fix Data Type
Rename States
Section  7 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Returns (all)
 
Clean Notes/Approver
 
7.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          9
        

        

          Clean Steps #
          1
        

        

          Actions #
          7
        
7.2. Returns (all) - Input Step
Step Type
Connection Name
Actions

          

            LoadExcel
            return reasons_new.xlsx
            RemoveColumns
            
            
          

        
7.2.1 Input Fields
Field Name
Field Type
Collation
                    Row ID                   integer                                   
                    Order Date                   string                   LEN_RIN_S2                
                    Order ID                   string                   LEN_RIN_S2                
                    Product ID                   string                   LEN_RIN_S2                
                    Sub-Category                   string                   LEN_RIN_S2                
                    Manufacturer                   string                   LEN_RIN_S2                
                    Product Name                   string                   LEN_RIN_S2                
                    Return Reason                   string                   LEN_RIN_S2                
                    Notes                   string                   LEN_RIN_S2                
7.2.1 Remove Columns Action
Column Names
Row ID
Order Date
Sub-Category
Manufacturer
Product Name

       

           
               7.3. Clean Notes/Approver - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         renamed Notes - Split 2 to Approver                        RenameColumn                    
                         Remove Notes                        RemoveColumn                    
                         renamed Notes - Split 1 to Return Notes                        RenameColumn                    
                         Clean 1                        Remap                    
                         Add Notes - Split 1                        AddColumn                    
                         Add Notes - Split 2                        AddColumn                    

       

   
7.3.1 Remove Column Action
Action Name
Column Name
Remove NotesNotes
7.3.2 Rename Column Action
Action Name
Column Name
Rename
renamed Notes - Split 2 to ApproverNotes - Split 2                           Approver
renamed Notes - Split 1 to Return NotesNotes - Split 1                           Return Notes
7.3.3 Add Column Action
Action Name
Column Name
Expression
Add Notes - Split 1Notes - Split 1                           TRIM( SPLIT( [Notes], "-", 1 ) )
Add Notes - Split 2Notes - Split 2                           TRIM( SPLIT( [Notes], "-", 2 ) )
7.3.4 Remap Action
Action Name
Column Name
Values
Clean 1Approver                           {"\"M Gomez\"":["\"M/ Gomez\"","\"M. Gomez\"","\"M Gomez\""],"\"S Kelly\"":["\"S Kelly\"","\"S. Kelly\""],"\"F Azad\"":["\"F. Azad\"","\"F Azad\""],"\"L Smith\"":["\"L. Smith\"","\"L Smith\""],"\"G Lindsay\"":["\"G. Lindsay\"","\"G Lindsay\""],"\"R Chen\"":["\"R Chen\"","\"R. Chen\""],"\"K Lawrence\"":["\"K. Lawrence\"","\"K Lawrence\""],"\"L Jenkins\"":["\"L. Jenkins\"","\"L Jenkins\""],"\"R Duchesne\"":["\"R. Duchesne\"","\"R Duchesne\""]}                        
Section  8 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
8.1 Summary Stats
Counts

        

          Join Steps #
          1
        

        

          Aggregate Steps #
          1
        

        

          Clean Steps #
          1
        

        

          Actions #
          11
        
8.2. Orders + Returns - Join Step
Step Name
Actions - Before Join
Conditions
Join Type
Sources
                       Orders + ReturnsChange Discount to Number (decimal) 1 (ChangeColumnType) - Right
Add Days to Ship (AddColumn) - Right
Add Returned? (AddColumn) - Left
Remove Table Names 1 (RemoveColumns) - Right
Remove File Paths 1 (RemoveColumns) - Right
Add Discount (AddColumn) - Right
Add Year of Sale (AddColumn) - Right
Exclude Discount:(17 - 18) 1 (RangeFilter) - Right		[{"leftExpression":"[Product ID]","rightExpression":"[Product ID]","comparator":"=="},{"leftExpression":"[Order ID]","rightExpression":"[Order ID]","comparator":"=="}]                      right                    All Orders
Clean Notes/Approver
8.2.1 Remove Columns Action
Action Name
Column Names
Is Before
Name Space
Remove Table Names 1Table Namestrue                    Right
Remove File Paths 1File Pathstrue                    Right
8.2.2 Range Filter Action
Action Name
Ranges
Is Before
Name Space
Exclude Discount:(17 - 18) 1Discount-->[{"startValue":"17.0","includeStart":true,"endValue":"18.0","includeEnd":false}]true                    Right
8.2.3 Add Column Action
Action Name
Column Name
Expression
Is Before
Name Space
Add Days to ShipDays to Ship                           DATEDIFF('day',[Order Date],[Ship Date])true                    Right
Add Returned?Returned?                           IF ISNULL([Return Reason]) = false  
THEN "Yes"
ELSE "No"
ENDtrue                    Left
Add DiscountDiscount                           IFNULL([Discount],0)true                    Right
Add Year of SaleYear of Sale                           YEAR([Order Date])true                    Right
8.2.4 Change Column Type Action
Action Name
Column Name
Type
Calculation
Is Before
Name Space
Change Discount to Number (decimal) 1Discount                           real                           true                    Right

       

           
               8.3. Clean 2 - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         renamed Product ID-1 to Product ID 1                        RenameColumn                    
                         Remove Product ID 1                        RemoveColumns                    
                         Remove Order ID-1 1                        RemoveColumns                    

       

   
8.3.1 Remove Columns Action
Action Name
Column Names
Remove Product ID 1Product ID
Remove Order ID-1 1Order ID-1
8.3.2 Rename Column Action
Action Name
Column Name
Rename
renamed Product ID-1 to Product ID 1Product ID-1                           Product ID
8.4. Roll Up Sales - Aggregate Step
Step Name
Group By Fields
Aggregate Fields
                       Roll Up SalesRegion, Year of Sale                      Profit - SUM, Sales - SUM, Quantity - SUM, Discount - AVG
Section  9 
Quota
 
Pivot Quotas
 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Roll Up Sales
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
Quota + Orders
 
Create 'Annual Regional Performance.hyper'
 
9.1 Summary Stats
Counts

        

          Output Steps #
          1
        

        

          Join Steps #
          1
        
9.2. Quota + Orders - Join Step
Step Name
Conditions
Join Type
Sources
                       Quota + Orders[{"leftExpression":"[Region]","rightExpression":"[Region]","comparator":"=="},{"leftExpression":"[Year]","rightExpression":"[Year of Sale]","comparator":"=="}]                      inner                    Pivot Quotas
Roll Up Sales
9.3. Create 'Annual Regional Performance.hyper' - Output Step - Write to Hyper
Step Name
Step Type
Hyper Output File
TDS Output
                       Create 'Annual Regional Performance.hyper'                      WriteToHyper                      ~/My Tableau Prep Repository/Datasources/Annual Regional Performance.hyper                      ~/My Tableau Prep Repository/Datasources/Annual Regional Performance.tds
Flow to Create 'Superstore Sales.hyper' Output
Flow Diagram
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
Section  10 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
Orders (Central)
 
Fix Dates
 
10.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          24
        

        

          Clean Steps #
          1
        

        

          Actions #
          13
        
10.2. Orders (Central) - Input Step
Step Type
Connection Name
Contains Header

          

            LoadCsv
            Orders_Central.csv
            
            true
            
          

        
10.2.1 Input Fields
Field Name
Field Type
Collation
                    Row ID                   integer                                   
                    Order ID                   string                   LEN_RUS                
                    Ship Mode                   string                   LEN_RUS                
                    Customer ID                   string                   LEN_RUS                
                    Customer Name                   string                   LEN_RUS                
                    Segment                   string                   LEN_RUS                
                    Country                   string                   LEN_RUS                
                    City                   string                   LEN_RUS                
                    State                   string                   LEN_RUS                
                    Postal Code                   integer                                   
                    Product ID                   string                   LEN_RUS                
                    Category                   string                   LEN_RUS                
                    Sub-Category                   string                   LEN_RUS                
                    Product                   string                   LEN_RUS                
                    Sales                   real                                   
                    Quantity                   integer                                   
                    Profit                   real                                   
                    Order Year                   integer                                   
                    Order Month                   integer                                   
                    Order Day                   integer                                   
                    Ship Year                   integer                                   
                    Ship Month                   integer                                   
                    Ship Day                   integer                                   
                    Discounts                   string                   LEN_RUS                

       

           
               10.3. Fix Dates - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         Remove Order Day                        RemoveColumn                    
                         renamed Discounts to Discount                        RenameColumn                    
                         Remove Ship Month                        RemoveColumn                    
                         Remove Ship Day                        RemoveColumn                    
                         Add Order Date                        AddColumn                    
                         change Order Date to date                        ChangeColumnType                    
                         Remove Order Month                        RemoveColumn                    
                         change Ship Date to date                        ChangeColumnType                    
                         Add Region                        AddColumn                    
                         Remove Order Year                        RemoveColumn                    
                         Add Ship Date                        AddColumn                    
                         renamed Product to Product Name                        RenameColumn                    
                         Remove Ship Year                        RemoveColumn                    

       

   
10.3.1 Remove Column Action
Action Name
Column Name
Remove Order DayOrder Day
Remove Ship MonthShip Month
Remove Ship DayShip Day
Remove Order MonthOrder Month
Remove Order YearOrder Year
Remove Ship YearShip Year
10.3.2 Rename Column Action
Action Name
Column Name
Rename
renamed Discounts to DiscountDiscounts                           Discount
renamed Product to Product NameProduct                           Product Name
10.3.3 Add Column Action
Action Name
Column Name
Expression
Add Order DateOrder Date                           (STR([Order Day])+"/"+STR([Order Month])+"/"+STR([Order Year]))
Add RegionRegion                           "Central"
Add Ship DateShip Date                           STR([Ship Day])+"/"+STR([Ship Month])+"/"+STR([Ship Year])
10.3.4 Change Column Type Action
Action Name
Column Name
Type
Calculation
change Order Date to dateOrder Date                           date                           IF NOT ISNULL(DATE(DATEPARSE("dd/MM/yyyy",[Order Date]))) THEN DATE(DATEPARSE("dd/MM/yyyy",[Order Date])) ELSEIF NOT ISNULL(DATE([Order Date])) THEN DATE([Order Date]) ELSE NULL END
change Ship Date to dateShip Date                           date                           IF NOT ISNULL(DATE(DATEPARSE("dd/MM/yyyy",[Ship Date]))) THEN DATE(DATEPARSE("dd/MM/yyyy",[Ship Date])) ELSEIF NOT ISNULL(DATE([Ship Date])) THEN DATE([Ship Date]) ELSE NULL END
Section  11 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
orders_south_2015
 
Remove Nulls
 
11.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          22
        

        

          Clean Steps #
          1
        

        

          Actions #
          1
        
11.2. orders_south_2015 - Input Step
Step Type
Connection Name
Contains Header

          

            LoadCsvInputUnion
            orders_south_2015.csv
            
            true
            
          

        
11.2.1 Input Fields
Field Name
Field Type
Collation
                    Sales                   real                                   
                    Quantity                   integer                                   
                    Profit                   real                                   
                    Discount                   real                                   
                    Region                   string                   LEN_RUS                
                    State                   string                   LEN_RUS                
                    Row ID                   integer                                   
                    Order ID                   string                   LEN_RUS                
                    Order Date                   datetime                                   
                    Ship Date                   datetime                                   
                    Ship Mode                   string                   LEN_RUS                
                    Customer ID                   string                   LEN_RUS                
                    Customer Name                   string                   LEN_RUS                
                    Segment                   string                   LEN_RUS                
                    Country                   string                   LEN_RUS                
                    City                   string                   LEN_RUS                
                    Postal Code                   integer                                   
                    Product ID                   string                   LEN_RUS                
                    Category                   string                   LEN_RUS                
                    Sub-Category                   string                   LEN_RUS                
                    Product Name                   string                   LEN_RUS                
                    File Paths                   string                   LEN_RUS                

       

           
               11.3. Remove Nulls - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         Exclude Order ID:null                        RangeFilter                    

       

   
11.3.1 Range Filter Action
Action Name
Ranges
Exclude Order ID:nullOrder ID-->[{"startValue":null,"includeStart":true,"endValue":null,"includeEnd":true}]
Section  12 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
Orders_East
 
Fix Data Type
 
12.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          21
        

        

          Clean Steps #
          1
        

        

          Actions #
          3
        
12.2. Orders_East - Input Step
Step Type
Connection Name

          

            LoadExcel
            Orders_East.xlsx
            
            
            
          

        
12.2.1 Input Fields
Field Name
Field Type
Collation
                    Category                   string                   LEN_RIN_S2                
                    City                   string                   LEN_RIN_S2                
                    Country                   string                   LEN_RIN_S2                
                    Customer ID                   string                   LEN_RIN_S2                
                    Customer Name                   string                   LEN_RIN_S2                
                    Discount                   real                                   
                    Order Date                   date                                   
                    Order ID                   string                   LEN_RIN_S2                
                    Postal Code                   integer                                   
                    Product ID                   string                   LEN_RIN_S2                
                    Product Name                   string                   LEN_RIN_S2                
                    Profit                   real                                   
                    Quantity                   integer                                   
                    Region                   string                   LEN_RIN_S2                
                    Row ID                   integer                                   
                    Segment                   string                   LEN_RIN_S2                
                    Ship Date                   date                                   
                    Ship Mode                   string                   LEN_RIN_S2                
                    State                   string                   LEN_RIN_S2                
                    Sub-Category                   string                   LEN_RIN_S2                
                    Sales                   string                   LEN_RIN_S2                

       

           
               12.3. Fix Data Type - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         change Discount to string 1                        ChangeColumnType                    
                         Quick Calc 1                        QuickCalcColumn                    
                         change Sales to real 1                        ChangeColumnType                    

       

   
12.3.1 Change Column Type Action
Action Name
Column Name
Type
Calculation
change Discount to string 1Discount                           string                           
change Sales to real 1Sales                           real                           
12.3.2 Calculated Field Action
Action Name
Column Name
Expression
Calc. Expression Type
Quick Calc 1Sales                           REGEXP_REPLACE([Sales], '[:Letter:]', '')                           RemoveLetters
Section  13 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
Orders (West)
 
Rename States
 
13.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          41
        

        

          Clean Steps #
          1
        

        

          Actions #
          2
        
13.2. Orders (West) - Input Step
Step Type
Connection Name
Actions
Contains Header

          

            LoadCsv
            Orders_West.csv
            RemoveColumns
            true
            
          

        
13.2.1 Input Fields
Field Name
Field Type
Collation
                    Row ID                   integer                                   
                    Order ID                   string                   LEN_RUS                
                    Order Date                   datetime                                   
                    Ship Date                   datetime                                   
                    Ship Mode                   string                   LEN_RUS                
                    Customer ID                   string                   LEN_RUS                
                    Customer Name                   string                   LEN_RUS                
                    Segment                   string                   LEN_RUS                
                    Country                   string                   LEN_RUS                
                    City                   string                   LEN_RUS                
                    Postal Code                   integer                                   
                    Region                   string                   LEN_RUS                
                    Product ID                   string                   LEN_RUS                
                    Category                   string                   LEN_RUS                
                    Sub-Category                   string                   LEN_RUS                
                    Product Name                   string                   LEN_RUS                
                    Sales                   real                                   
                    Quantity                   integer                                   
                    Discount                   real                                   
                    Profit                   real                                   
                    Right_Row ID                   integer                                   
                    Right_Order Date                   datetime                                   
                    Right_Ship Date                   datetime                                   
                    Right_Ship Mode                   string                   LEN_RUS                
                    Right_Customer ID                   string                   LEN_RUS                
                    Right_Customer Name                   string                   LEN_RUS                
                    Right_Segment                   string                   LEN_RUS                
                    Right_Country                   string                   LEN_RUS                
                    Right_City                   string                   LEN_RUS                
                    Right_State2                   string                   LEN_RUS                
                    Right_Postal Code                   integer                                   
                    Right_Region                   string                   LEN_RUS                
                    Right_Product ID                   string                   LEN_RUS                
                    Right_Category                   string                   LEN_RUS                
                    Right_Sub-Category                   string                   LEN_RUS                
                    Right_Product Name                   string                   LEN_RUS                
                    Right_Sales                   real                                   
                    Right_Quantity                   integer                                   
                    Right_Discount                   real                                   
                    Right_Profit                   real                                   
                    State                   string                   LEN_RUS                
13.2.1 Remove Columns Action
Column Names
Right_Row ID
Right_Order Date
Right_Ship Date
Right_Ship Mode
Right_Customer ID
Right_Customer Name
Right_Segment
Right_Country
Right_City
Right_State2
Right_Postal Code
Right_Region
Right_Product ID
Right_Category
Right_Sub-Category
Right_Product Name
Right_Sales
Right_Quantity
Right_Discount
Right_Profit

       

           
               13.3. Rename States - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         18 values replaced with 9 values.                        Remap                    

       

   
13.3.1 Remap Action
Action Name
Column Name
Values
18 values replaced with 9 values.State                           {"\"Arizona\"":["\"AZ\"","\"Arizona\""],"\"California\"":["\"CA\"","\"California\""],"\"Colorado\"":["\"CO\"","\"Colorado\""],"\"Idaho\"":["\"ID\"","\"Idaho\""],"\"Montana\"":["\"MT\"","\"Montana\""],"\"New Mexico\"":["\"NM\"","\"New Mexico\""],"\"Oregon\"":["\"OR\"","\"Oregon\""],"\"Washington\"":["\"WA\"","\"Washington\""],"\"Utah\"":["\"UT\"","\"Utah\""]}                        
Section  14 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
All Orders
 
14.1 Summary Stats
Counts

        

          Union Steps #
          1
        
14.2. All Orders - Union Step
Step Name
Sources
                       All OrdersFix Dates
Remove Nulls
Fix Data Type
Rename States
Section  15 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
Returns (all)
 
Clean Notes/Approver
 
15.1 Summary Stats
Counts

        

          Input Steps #
          1
        

        

          Input Fields #
          9
        

        

          Clean Steps #
          1
        

        

          Actions #
          7
        
15.2. Returns (all) - Input Step
Step Type
Connection Name
Actions

          

            LoadExcel
            return reasons_new.xlsx
            RemoveColumns
            
            
          

        
15.2.1 Input Fields
Field Name
Field Type
Collation
                    Row ID                   integer                                   
                    Order Date                   string                   LEN_RIN_S2                
                    Order ID                   string                   LEN_RIN_S2                
                    Product ID                   string                   LEN_RIN_S2                
                    Sub-Category                   string                   LEN_RIN_S2                
                    Manufacturer                   string                   LEN_RIN_S2                
                    Product Name                   string                   LEN_RIN_S2                
                    Return Reason                   string                   LEN_RIN_S2                
                    Notes                   string                   LEN_RIN_S2                
15.2.1 Remove Columns Action
Column Names
Row ID
Order Date
Sub-Category
Manufacturer
Product Name

       

           
               15.3. Clean Notes/Approver - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         renamed Notes - Split 2 to Approver                        RenameColumn                    
                         Remove Notes                        RemoveColumn                    
                         renamed Notes - Split 1 to Return Notes                        RenameColumn                    
                         Clean 1                        Remap                    
                         Add Notes - Split 1                        AddColumn                    
                         Add Notes - Split 2                        AddColumn                    

       

   
15.3.1 Remove Column Action
Action Name
Column Name
Remove NotesNotes
15.3.2 Rename Column Action
Action Name
Column Name
Rename
renamed Notes - Split 2 to ApproverNotes - Split 2                           Approver
renamed Notes - Split 1 to Return NotesNotes - Split 1                           Return Notes
15.3.3 Add Column Action
Action Name
Column Name
Expression
Add Notes - Split 1Notes - Split 1                           TRIM( SPLIT( [Notes], "-", 1 ) )
Add Notes - Split 2Notes - Split 2                           TRIM( SPLIT( [Notes], "-", 2 ) )
15.3.4 Remap Action
Action Name
Column Name
Values
Clean 1Approver                           {"\"M Gomez\"":["\"M/ Gomez\"","\"M. Gomez\"","\"M Gomez\""],"\"S Kelly\"":["\"S Kelly\"","\"S. Kelly\""],"\"F Azad\"":["\"F. Azad\"","\"F Azad\""],"\"L Smith\"":["\"L. Smith\"","\"L Smith\""],"\"G Lindsay\"":["\"G. Lindsay\"","\"G Lindsay\""],"\"R Chen\"":["\"R Chen\"","\"R. Chen\""],"\"K Lawrence\"":["\"K. Lawrence\"","\"K Lawrence\""],"\"L Jenkins\"":["\"L. Jenkins\"","\"L Jenkins\""],"\"R Duchesne\"":["\"R. Duchesne\"","\"R Duchesne\""]}                        
Section  16 
Orders (Central)
 
Fix Dates
 
orders_south_2015
 
Remove Nulls
 
Orders_East
 
Fix Data Type
 
Orders (West)
 
Rename States
 
All Orders
 
Returns (all)
 
Clean Notes/Approver
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
Orders + Returns
 
Clean 2
 
Create 'Superstore Sales.hyper'
 
16.1 Summary Stats
Counts

        

          Output Steps #
          1
        

        

          Join Steps #
          1
        

        

          Clean Steps #
          1
        

        

          Actions #
          11
        
16.2. Orders + Returns - Join Step
Step Name
Actions - Before Join
Conditions
Join Type
Sources
                       Orders + ReturnsChange Discount to Number (decimal) 1 (ChangeColumnType) - Right
Add Days to Ship (AddColumn) - Right
Add Returned? (AddColumn) - Left
Remove Table Names 1 (RemoveColumns) - Right
Remove File Paths 1 (RemoveColumns) - Right
Add Discount (AddColumn) - Right
Add Year of Sale (AddColumn) - Right
Exclude Discount:(17 - 18) 1 (RangeFilter) - Right		[{"leftExpression":"[Product ID]","rightExpression":"[Product ID]","comparator":"=="},{"leftExpression":"[Order ID]","rightExpression":"[Order ID]","comparator":"=="}]                      right                    All Orders
Clean Notes/Approver
16.2.1 Remove Columns Action
Action Name
Column Names
Is Before
Name Space
Remove Table Names 1Table Namestrue                    Right
Remove File Paths 1File Pathstrue                    Right
16.2.2 Range Filter Action
Action Name
Ranges
Is Before
Name Space
Exclude Discount:(17 - 18) 1Discount-->[{"startValue":"17.0","includeStart":true,"endValue":"18.0","includeEnd":false}]true                    Right
16.2.3 Add Column Action
Action Name
Column Name
Expression
Is Before
Name Space
Add Days to ShipDays to Ship                           DATEDIFF('day',[Order Date],[Ship Date])true                    Right
Add Returned?Returned?                           IF ISNULL([Return Reason]) = false  
THEN "Yes"
ELSE "No"
ENDtrue                    Left
Add DiscountDiscount                           IFNULL([Discount],0)true                    Right
Add Year of SaleYear of Sale                           YEAR([Order Date])true                    Right
16.2.4 Change Column Type Action
Action Name
Column Name
Type
Calculation
Is Before
Name Space
Change Discount to Number (decimal) 1Discount                           real                           true                    Right

       

           
               16.3. Clean 2 - Clean Step
           
       

   
       

           
               

                   
                       
Clean Action

                   		
                   
                       
Node Type

                   		
               

           
           
                         renamed Product ID-1 to Product ID 1                        RenameColumn                    
                         Remove Product ID 1                        RemoveColumns                    
                         Remove Order ID-1 1                        RemoveColumns                    

       

   
16.3.1 Remove Columns Action
Action Name
Column Names
Remove Product ID 1Product ID
Remove Order ID-1 1Order ID-1
16.3.2 Rename Column Action
Action Name
Column Name
Rename
renamed Product ID-1 to Product ID 1Product ID-1                           Product ID
16.4. Create 'Superstore Sales.hyper' - Output Step - Write to Hyper
Step Name
Step Type
Hyper Output File
TDS Output
                       Create 'Superstore Sales.hyper'                      WriteToHyper                      ~/My Tableau Prep Repository/Datasources/Superstore Sales.hyper                      ~/My Tableau Prep Repository/Datasources/Superstore Sales.tds